package com.vince.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.vince.model.domain.Order;
import com.vince.model.domain.User;
import com.vince.util.DBUtil;

/**
 * 管理员对应的订单的处理操作
 * @author susp
 *
 */
@WebServlet("/servlet/orderservlet")
public class OrderServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
   
    public OrderServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		/**
		 * 登录管理员对应的所有表单的列表(所有管理员的订单都保存在torder表下)
		 *
		 */
		//创建个表格,上线时后台数据库已经创建好了
//		String sql="create table torder("
//		        +"order_id int(11) not null auto_increment primary key,"
//				+"order_code varchar(100),"
//		        +"buyer varchar(100),"
//				+"sum double(9,3),"
//		        +"delivery_method varchar(200),"
//				+"last_modify_date datetime,"
//		        +"created_date datetime"
//				+")";
//		DBUtil mysql=new DBUtil();
//		try {
//			Connection conn=mysql.getConnection();
//			Statement sm=conn.createStatement();
//			sm.execute(sql);
//			
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		create table order(
//        order_id int(11) not null auto_increment primary key,
//		order_code varchar(100),
//        buyer varchar(100),
//		sum double(9,3),
//        delivery_method varchar(200),
//		last_modify_date datetime,
//        created_date datetime
//		);
//		
		
		String method=request.getParameter("method");

		if("list".equals(method)){
			//创建对象及集合，用于保存查询出的order订单对象
			User user= (User) request.getSession().getAttribute("user");//从session获取name该登录用户的name
			String name=user.getUsername();
			System.out.println("name:"+user.getUsername());
			//按照管理员name查询对应的所有订单
			String sql="select order_id,order_code,buyer,sum,delivery_method,last_modify_date,created_date from torder where buyer=?";
			DBUtil mysql=new DBUtil();
			PreparedStatement ps=null;
			ResultSet rs=null;
			Connection conn=null;
			List<Order> orderList=new ArrayList<Order>();
	
			try {
				conn=mysql.getConnection();
				ps=conn.prepareStatement(sql);
				ps.setString(1, name);
				rs=ps.executeQuery();
				while(rs.next()){
					Order order=new Order();
					order.setOrderId(rs.getInt(1));
					order.setOrderCode(rs.getString(2));
					order.setBuyer(rs.getString(3));//buyer跟用户名对应
					order.setSum(rs.getDouble(4));
					order.setDeliveryMethod(rs.getString(5));
					order.setLastModifyDate(rs.getDate(6));
					order.setCreateddate(rs.getDate(7));
					System.out.println(order.getOrderCode());
					System.out.println("orderlist-------------"+order.getOrderCode());
					orderList.add(order);
				
				}
							
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				mysql.releaseResource(conn, ps, rs);
			}
			response.setCharacterEncoding("utf-8");
			request.setAttribute("orderlist", orderList);
			request.getRequestDispatcher("/view/orders/orderManage/orderlist.jsp").forward(request, response);//转发
			
		}else if("add".equals(method)){
			//管理员增加新的订单，正常逻辑应该是客户下单之后新增一个订单
			List<Order> orderList=new ArrayList<Order>();
			
			//先检验session是否过期，过期则重新登录，不过期则执行
			if(request.getSession().getAttribute("user")==null){
				//重定向到登录界面
				response.sendRedirect(request.getContextPath()+"/view/share/session/session.jsp");
			}else{
				//前台需要检测不为空才跳转到后台
				String ordercode=request.getParameter("name");
//				String ordersum=request.getParameter("sum");
				String deliverymethod=request.getParameter("deliverymethod");
				User user=(User)request.getSession().getAttribute("user");
				String buyer=user.getUsername();
			    Order order=new Order();
			    order.setOrderCode(ordercode);
//			    order.setSum(Double.parseDouble(ordersum));
			    order.setBuyer(buyer);
			    order.setDeliveryMethod(deliverymethod);
			    DBUtil mysql=new DBUtil();
			    Connection conn = null;
				PreparedStatement ps = null;
				int result=0;
				String sql="insert into torder(order_code,buyer,delivery_method,last_modify_date,created_date) "
			    		+ "values(?,?,?,now(),now())";
			    
			    try {
					conn=mysql.getConnection();
					ps=conn.prepareStatement(sql);
					ps.setString(1,order.getOrderCode());
					ps.setString(2, order.getBuyer());
					ps.setString(3, order.getDeliveryMethod());
					result=ps.executeUpdate();
					if(result==1){
						System.out.println("插入成功");
						//取出添加到orderlist中
						String sql2="select order_id,order_code,buyer,sum,delivery_method,last_modify_date,created_date from torder where buyer=?";

						PreparedStatement ps2=conn.prepareStatement(sql2);
						ps2.setString(1, buyer);
						ResultSet rs=ps2.executeQuery();
						while(rs.next()){
							System.out.println("进入...........");
							Order order2=new Order();
							order2.setOrderId(rs.getInt(1));
							order2.setOrderCode(rs.getString(2));
							order2.setBuyer(rs.getString(3));//buyer跟用户名对应
							order2.setSum(rs.getDouble(4));
							order2.setDeliveryMethod(rs.getString(5));
							order2.setLastModifyDate(rs.getDate(6));
							order2.setCreateddate(rs.getDate(7));
							System.out.println(order2.getOrderCode());
							orderList.add(order2);
						
						}
			
						//成功后，讲新增的添加到orderlist中，重定向到list.jsp可继续添加
						//按照管理员name查询对应的所有订单
						request.setAttribute("orderlist", orderList);
						request.getRequestDispatcher("/view/orders/orderManage/orderlist.jsp").forward(request, response);//转发
					}else{
						System.out.println("插入数据失败");
						
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					
					mysql.releaseResource(conn, ps, null);
				}
		
			}
			
		}else if("search".equals(method)){
			
			//查询指定日期的订单
			String beginTime=request.getParameter("begin");
			String endTime=request.getParameter("end");
			System.out.println("起始时间："+beginTime);
			System.out.println("结束时间："+endTime);
			User user1=new User();
			
			
			//select order_id,order_code,buyer,sum,delivery_method,last_modify_date,created_date from torder where to_days(created_date) between str_to_date("2016-03-22",'%Y-%m-%d') and str_to_date("2016-03-22",'%Y-%m-%d') 
			
			String sql="select order_id,order_code,buyer,sum,delivery_method,last_modify_date,"
					+ "created_date from torder where date(created_date) between "
					+ "str_to_date(?,'%Y-%m-%d') and str_to_date(?,'%Y-%m-%d')";
			DBUtil mysql=new DBUtil();
			PreparedStatement ps=null;
			ResultSet rs=null;
			List<Order> orderlist=new ArrayList<Order>();
			try {
				Connection conn=mysql.getConnection();
				ps=conn.prepareStatement(sql);
				ps.setString(1, beginTime);
				ps.setString(2, endTime);
				rs=ps.executeQuery();
				while(rs.next()){
					System.out.println("查询到数据.................");
					Order order=new Order();
					order.setOrderId(rs.getInt(1));
					order.setOrderCode(rs.getString(2));
					order.setBuyer(rs.getString(3));
					order.setSum(rs.getDouble(4));
					order.setDeliveryMethod(rs.getString(5));
					order.setLastModifyDate(rs.getDate(6));
					order.setCreateddate(rs.getDate(7));
					orderlist.add(order);
					
				}
				request.setAttribute("orderlist", orderlist);
				request.getRequestDispatcher("/view/orders/orderManage/orderlist.jsp").forward(request, response);
				
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
					
		}else if("delete".equals(method)){
			//1.删除订单，2.删除里面所有的订单详情
			String id=request.getParameter("orderid");
			System.out.println("orderservlet..delete..........."+id);
			List<Order> orderlist=new ArrayList<Order>();
			//删除订单
			String sql="delete from torder where order_id=?";
			DBUtil mysql=new DBUtil();
			int result=0;
			try {
				Connection conn=mysql.getConnection();
				PreparedStatement ps=conn.prepareStatement(sql);
				ps.setInt(1, Integer.valueOf(id));
				result=ps.executeUpdate();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//删除该订单下的详情
			String sql2="delete from itemsorder where order_id=?";
			Connection conn;
			try {
				conn = mysql.getConnection();
				PreparedStatement ps2=conn.prepareStatement(sql2);
				ps2.setInt(1, Integer.valueOf(id));
				ps2.execute();
	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//查询orderlist，以便返回到orderlist.jsps
			String sql3="select order_id,order_code,buyer,sum,delivery_method,last_modify_date,created_date from torder where buyer=?";

			PreparedStatement ps2;
			PreparedStatement ps3;
			try {
				conn=mysql.getConnection();
				ps3 = conn.prepareStatement(sql3);
				ps3.setString(1, ((User)request.getSession().getAttribute("user")).getUsername());
				ResultSet rs3=ps3.executeQuery();
				while(rs3.next()){
					Order order2=new Order();
					order2.setOrderId(rs3.getInt(1));
					order2.setOrderCode(rs3.getString(2));
					order2.setBuyer(rs3.getString(3));//buyer跟用户名对应
					order2.setSum(rs3.getDouble(4));
					order2.setDeliveryMethod(rs3.getString(5));
					order2.setLastModifyDate(rs3.getDate(6));
					order2.setCreateddate(rs3.getDate(7));
					System.out.println("....."+order2.getOrderCode());
					orderlist.add(order2);
				
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			request.setAttribute("orderlist", orderlist);
			request.getRequestDispatcher("/view/orders/orderManage/orderlist.jsp").forward(request, response);;//转发
			
			
		}
	
	
	
	
	}
}
